<?php
    /**
    * Get profile list for ajax in manage grid
    */
    if (!defined('EXPONENT')) exit('');
    if (exponent_users_isAdmin() ||  exponent_permissions_check(array("administrate","upload","delete","meeting","meeting_delete","meeting_assistant"),exponent_core_makeLocation("profilemodule","_syscore","")))
    {  
        global $user;
        global $db;

        if (isset($_REQUEST['search']))
        {         
            // get search type
            // all ; subject; lawyer; mentor
            $search_type = $_REQUEST['search_type'];
            $search = $_REQUEST['search'];
            
            $sql_where = 'WHERE 1 ';
            
            $sql="SELECT {$db->prefix}profilemodule_profile.* FROM {$db->prefix}profilemodule_profile INNER JOIN {$db->prefix}profilemodule_{$search_type} ON {$db->prefix}profilemodule_profile.user_id = {$db->prefix}profilemodule_{$search_type}.user_id ";
            
            $sql_count="SELECT count({$db->prefix}profilemodule_profile.id) c FROM {$db->prefix}profilemodule_profile INNER JOIN {$db->prefix}profilemodule_{$search_type} ON {$db->prefix}profilemodule_profile.user_id = {$db->prefix}profilemodule_{$search_type}.user_id ";
            $group_by =" GROUP BY {$db->prefix}profilemodule_profile.user_id ";
            if ($search_type == "meeting")
            {
                if (isset($_REQUEST['meeting_new_only']))
                    $sql_where.=" AND (approve_state = 0)";
                    
                if ($search != "")
                    $sql_where.=" AND (mentor_location LIKE '%{$search}%' OR mentor_language LIKE '%{$search}%' OR mentor_required LIKE '%{$search}%' OR previous_subject LIKE '%{$search}%' OR new_subject LIKE '%{$search}%' OR meeting_notes LIKE '%{$search}%' OR meeting_place LIKE '%{$search}%'  OR related_lawyers LIKE '%{$search}%')";
                 
                if (isset($_REQUEST['datefrom']) && $_REQUEST['datefrom']!="")
                {
                    $date_from=strtotime($_REQUEST['datefrom']);
                    $sql_where.=" AND (meeting_date>={$date_from})";
                }
                
                if (isset($_REQUEST['dateto']) && $_REQUEST['dateto']!="")
                {
                    $date_to=strtotime($_REQUEST['dateto']);
                    $sql_where.=" AND (meeting_date<={$date_to})";
                }
            }
            elseif ($search_type == "cases")
            {
                if ($search != "")
                    $sql_where.=" AND (name LIKE '%{$search}%')";
                if (isset($_REQUEST['datefrom']) && $_REQUEST['datefrom']!="")
                {
                    $date_from=strtotime($_REQUEST['datefrom']);
                    $sql_where.=" AND (post_date>={$date_from})";
                }
                
                if (isset($_REQUEST['dateto']) && $_REQUEST['dateto']!="")
                {
                    $date_to=strtotime($_REQUEST['dateto']);
                    $sql_where.=" AND (post_date<={$date_to})";
                }
            }
            elseif ($search_type == "documents")
            {
                if ($search != "")
                    $sql_where.=" AND (name LIKE '%{$search}%')";
                if (isset($_REQUEST['datefrom']) && $_REQUEST['datefrom']!="")
                {
                    $date_from=strtotime($_REQUEST['datefrom']);
                    $sql_where.=" AND (post_date>={$date_from})";
                }
                
                if (isset($_REQUEST['dateto']) && $_REQUEST['dateto']!="")
                {
                    $date_to=strtotime($_REQUEST['dateto']);
                    $sql_where.=" AND (post_date<={$date_to})";
                }
            }
            elseif ($search_type == "invoices")
            {
                if ($search != "")
                    $sql_where.=" AND (name LIKE '%{$search}%')";
                if (isset($_REQUEST['datefrom']) && $_REQUEST['datefrom']!="")
                {
                    $date_from=strtotime($_REQUEST['datefrom']);
                    $sql_where.=" AND (post_date>={$date_from})";
                }
                
                if (isset($_REQUEST['dateto']) && $_REQUEST['dateto']!="")
                {
                    $date_to=strtotime($_REQUEST['dateto']);
                    $sql_where.=" AND (post_date<={$date_to})";
                }
            }
            else{
                $sql="SELECT * FROM {$db->prefix}profilemodule_profile  ";
                $sql_count="SELECT count(id) c FROM {$db->prefix}profilemodule_profile  ";
                $group_by = "";
                if ($search!="")
                {
                    $sql_where.=" AND (firstname LIKE '%{$search}%' OR company_name LIKE '%{$search}%' OR lastname LIKE '%{$search}%' OR tc_identity LIKE '%{$search}%' OR email LIKE '%{$search}%' OR website LIKE '%{$search}%' OR address LIKE '%{$search}%'  OR tel1 LIKE '%{$search}%' OR tel2 LIKE '%{$search}%' OR skype LIKE '%{$search}%' OR fax LIKE '%{$search}%' OR tax_number LIKE '%{$search}%' OR city LIKE '%{$search}%' OR country LIKE '%{$search}%' OR tax_office LIKE '%{$search}%' OR postcode LIKE '%{$search}%')";
                }
            }
            
            
            $page=1;
            if (isset($_REQUEST['page']))
                $page=intval($_REQUEST['page']);
            $pageSize=10;

            if (isset($_REQUEST['rows']))
                $pageSize=intval($_REQUEST['rows']);
            $start=($page-1)*$pageSize;

            // sort
            $sort="";
            if (isset($_REQUEST['sortName'])){
                $sort = " ORDER BY {$_REQUEST['sortName']} {$_REQUEST['sortOrder']}";
            }

            $data=$db->selectObjectsBySql("{$sql} {$sql_where} {$group_by} {$sort} LIMIT {$start},{$pageSize}"); 

            $ret_object = null;
            $ret_object->total = $db->countObjectsBySql("{$sql_count} {$sql_where} {$group_by}");
            $ret_object->rows = $data; 
            echo json_encode($ret_object);
             
        }
        else
        {
        }
    }

?>